package com.uniview.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.uniview.dao.OrderCountDao;
import com.uniview.dao.ServiceOrdersDao;
import com.uniview.dao.ServiceOrdersLogsDao;
import com.uniview.entity.po.ServiceOrders;
import com.uniview.entity.po.ServiceOrdersLogs;
import com.uniview.entity.vo.Pager;
import com.uniview.service.ServiceOrdersService;

@Service("serviceOrdersService")
public class ServiceOrdersServiceImpl implements ServiceOrdersService {

	private Logger logger = Logger.getLogger(ServiceOrdersServiceImpl.class);

	@Autowired
	private ServiceOrdersDao serviceOrdersDao;

	@Autowired
	private ServiceOrdersLogsDao serviceOrdersLogsDao;

	@Autowired
	private OrderCountDao orderCountDao;

	@Override
	public Integer saveOrders(ServiceOrders os, ServiceOrdersLogs sol) {
		try {
			Integer orderId = (Integer) serviceOrdersDao.save(os);
			if (orderId > 0) {
				sol.setOrderId(orderId);
				serviceOrdersLogsDao.save(sol);
				return orderId;
			}
		} catch (Exception e) {
			logger.error("处理工单数据异常", e);
		}
		return 0;
	}

	@Override
	public boolean approveOrders(ServiceOrders os, ServiceOrdersLogs sol) {
		return this.updateOrders(os, sol);
	}

	@Override
	public boolean handleOrder(ServiceOrders os, ServiceOrdersLogs sol) {
		return this.updateOrders(os, sol);
	}

	@Override
	public boolean urgeOrder(ServiceOrders os, ServiceOrdersLogs sol) {
		return this.updateOrders(os, sol);
	}

	private boolean updateOrders(ServiceOrders os, ServiceOrdersLogs sol) {
		try {
			boolean result = serviceOrdersDao.update(os);
			if (result) {
				sol.setOrderId(os.getId());
				serviceOrdersLogsDao.save(sol);
				return result;
			}
		} catch (Exception e) {
			logger.error("处理工单数据异常", e);
		}
		return false;
	}

	@Override
	public ServiceOrders featchOrders(Integer orderId) throws Exception {
		return (ServiceOrders) serviceOrdersDao.find(orderId);
	}

	@Override
	public Pager<ServiceOrders> findOrdersByCreator(String creator, int status, int pageSize, int pageNo) {
		String sql = "select * from sd_orders where creator ='" + creator + "' & stauts = " + status
				+ " order by createTime Desc";
		try {
			return serviceOrdersDao.findPagerByQuery(pageNo, pageSize, sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<ServiceOrders> findOrdersByHandle(String handle, int status, int pageSize, int pageNo) {
		String sql = "from ServiceOrders where handleNo ='" + handle + "' and status = " + status
				+ " order by createTime Desc";
		try {
			return serviceOrdersDao.findPagerByQuery(pageNo, pageSize, sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<ServiceOrders> exportAllOrders(String creator, String handler, String empName, String region,
			String startDate, String endDate, int status, String searchOrderNo) {
		StringBuffer sql = new StringBuffer("select * from sd_orders where 1=1 and status <> -99 ");
		// 普通员工
		if (StringUtils.isBlank(region)) {
			if (StringUtils.isBlank(creator) && StringUtils.isBlank(handler)) {
				sql.append("and (creator like '%" + empName + "%' or handle like '%" + empName + "%')");
			} else if (StringUtils.isNotBlank(creator)) {
				sql.append("and creator like '%" + creator + "%' and creator like '%" + empName + "%' ");
			} else if (StringUtils.isNotBlank(handler)) {
				sql.append("and handle like '%" + handler + "%' and creator like '%" + empName + "%' ");
			} else {
				sql.append("and creator like '%" + creator + "%' and handle like '%" + handler
						+ "%' and creator like '%" + empName + "%' ");
			}
		}
		// 服务经理
		else {
			if (StringUtils.isBlank(creator) && StringUtils.isBlank(handler)) {
				sql.append("and (creator like '%" + empName + "%' or handle like '%" + empName
						+ "%' or handlerDept like '%" + region + "%')  ");
			} else if (StringUtils.isNotBlank(creator)) {
				sql.append("and creator like '%" + creator + "%' and handlerDept like '%" + region + "%' ");
			} else if (StringUtils.isNotBlank(handler)) {
				sql.append("and handle like '%" + handler + "%' and handlerDept like '%" + region + "%' ");
			} else {
				sql.append("and creator like '%" + creator + "%' and handle like '%" + handler
						+ "%' and handlerDept like '%" + region + "%' ");
			}
		}

		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			sql.append("and createTime between to_date('" + startDate + " 00:00:00"
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (status >= 0) {
			sql.append("and status =" + status + " ");
		}
		
		if (StringUtils.isNotBlank(searchOrderNo)) {
			sql.append("and orderno ='" + searchOrderNo + "' ");
		}
		sql.append(" order by createTime Desc");
		try {
			return serviceOrdersDao.findBySql(sql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<ServiceOrders> exportAllOrdersByAdmin(String creator, String handler, String startDate, String endDate,
			Integer status, String searchOrderNo) {
		StringBuffer sql = new StringBuffer("select * from sd_orders where 1=1 and status <> -99 ");

		if (StringUtils.isNotBlank(creator)) {
			sql.append("and creator like '%" + creator + "%' ");
		}

		if (StringUtils.isNotBlank(handler)) {
			sql.append("and handle like '%" + handler + "%' ");
		}

		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			sql.append("and createTime between to_date('" + startDate + " 00:00:00"
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (status >= 0) {
			sql.append("and status =" + status + " ");
		}
		
		if (StringUtils.isNotBlank(searchOrderNo)) {
			sql.append("and orderno = '" + searchOrderNo + "' ");
		}
		sql.append(" order by createTime Desc");
		try {
			return serviceOrdersDao.findBySql(sql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public Pager<ServiceOrders> findAllOrders(String creator, String handler, String startDate, String endDate,
			String area, int status, int pageSize, int pageNo) {
		StringBuffer buffer = new StringBuffer("from ServiceOrders where 1=1 ");
		if (StringUtils.isNotBlank(creator)) {
			buffer.append("and creator like '%" + creator + "%' ");
		}

		if (StringUtils.isNotBlank(handler)) {
			buffer.append("and handle like '%" + handler + "%' ");
		}

		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			buffer.append("and createTime between to_date('" + startDate + " 00:00:00"
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}

		if (status >= 0) {
			buffer.append("and status =" + status + " ");
		}
		buffer.append(" order by createTime Desc");
		try {
			return serviceOrdersDao.findPagerByQuery(pageNo, pageSize, buffer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<ServiceOrders> findAllOrdersByAdmin(String creator, String handler, String startDate, String endDate,
			int status, String orderNo, int pageSize, int pageNo) {
		StringBuffer buffer = new StringBuffer("from ServiceOrders where 1=1 and status <> -99");
		if (StringUtils.isNotBlank(creator)) {
			buffer.append("and creator like '%" + creator + "%' ");
		}

		if (StringUtils.isNotBlank(handler)) {
			buffer.append("and handle like '%" + handler + "%' ");
		}

		if (StringUtils.isNotBlank(orderNo)) {
			buffer.append("and orderNo like '%" + orderNo + "%' ");
		}

		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			buffer.append("and createTime between to_date('" + startDate + " 00:00:00"
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}

		if (status >= 0) {
			buffer.append("and status =" + status + " ");
		}
		buffer.append(" order by createTime Desc");
		try {
			return serviceOrdersDao.findPagerByQuery(pageNo, pageSize, buffer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<ServiceOrders> findAllOrders(String creator, String handler, String empName, String region,
			String startDate, String endDate, String area, int status, int pageSize, int pageNo) {
		StringBuffer buffer = new StringBuffer("from ServiceOrders where 1=1 ");
		// 普通员工
		if (StringUtils.isBlank(region)) {
			if (StringUtils.isBlank(creator) && StringUtils.isBlank(handler)) {
				buffer.append("and (creator like '%" + empName + "%' or handle like '%" + empName + "%') ");
			} else if (StringUtils.isNotBlank(creator)) {
				buffer.append("and creator like '%" + creator + "%' and creator like '%" + empName + "%' ");
			} else if (StringUtils.isNotBlank(handler)) {
				buffer.append("and handle like '%" + handler + "%' and creator like '%" + empName + "%' ");
			} else {
				buffer.append("and creator like '%" + creator + "%' and handle like '%" + handler
						+ "%' and creator like '%" + empName + "%' ");
			}
		}
		// 服务经理
		else {
			if (StringUtils.isBlank(creator) && StringUtils.isBlank(handler)) {
				buffer.append("and (creator like '%" + empName + "%' or handle like '%" + empName
						+ "%' or handlerDept like '%" + region + "%')  ");
			} else if (StringUtils.isNotBlank(creator)) {
				buffer.append("and creator like '%" + creator + "%' and handlerDept like '%" + region + "%' ");
			} else if (StringUtils.isNotBlank(handler)) {
				buffer.append("and handle like '%" + handler + "%' and handlerDept like '%" + region + "%' ");
			} else {
				buffer.append("and creator like '%" + creator + "%' and handle like '%" + handler
						+ "%' and handlerDept like '%" + region + "%' ");
			}
		}
		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			buffer.append("and createTime between to_date('" + startDate + " 00:00:00"
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (status >= 0) {
			buffer.append("and status =" + status + " ");
		}
		buffer.append(" order by modifyTime Desc");
		try {
			return serviceOrdersDao.findPagerByQuery(pageNo, pageSize, buffer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean deleteOrder(int orderId) throws Exception {
		return serviceOrdersDao.deleteById(orderId);
	}

	@Override
	public boolean updateOrder(ServiceOrders os, ServiceOrdersLogs sol) throws Exception {
		boolean result = serviceOrdersDao.update(os);
		if (result) {
			sol.setOrderId(os.getId());
			serviceOrdersLogsDao.save(sol);
			return result;
		}
		return result;
	}

	@Override
	@SuppressWarnings("unchecked")
	public Map<String, Object> countOrdersByStatusFromDay(String empCode, String department, int days) {
		try {
			List<?> list = orderCountDao.countOrdersFromDay(empCode, department, days);
			Map<String, Object> map = new HashMap<>();
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map1 = (Map<String, Object>) list.get(i);
				map.put("count" + map1.get("STATUS").toString(), map1.get("NUM"));
			}
			return map;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	@SuppressWarnings("unchecked")
	public Map<String, Object> countOrdersByStatusFromDayByAdmin(int days) {
		try {
			List<?> list = orderCountDao.countOrdersFromDayByAdmin(days);
			Map<String, Object> map = new HashMap<>();
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map1 = (Map<String, Object>) list.get(i);
				map.put("count" + map1.get("STATUS").toString(), map1.get("NUM"));
			}
			return map;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<ServiceOrders> findAllOrdersByWeb(Integer userRole, String empCode, String creator, String handler,
			String startDate, String endDate, int status, String orderNo, int pageSize, int pageNo) {
		String sql = "";
		String countSql = "";
		try {
			if (userRole == 2) {
				// 服务经理，获取所在办事情下属员工的工单，对应状态
				sql = "SELECT oo.* FROM ( SELECT o1.* FROM sd_orders o1 RIGHT JOIN ("
						+ "SELECT e1.empcode FROM sd_employees e1 RIGHT JOIN sd_regionmanager rm1 "
						+ "ON e1.orgname LIKE '%' || rm1.regionname || '%' WHERE UPPER (rm1.workno) = UPPER ('"
						+ empCode + "') " + "AND e1.EMPCODE <> upper('" + empCode
						+ "') ) ee1 ON UPPER (o1.handleno) = UPPER (ee1.empcode) "
						+ "UNION SELECT o2.* FROM sd_orders o2 RIGHT JOIN ("
						+ "SELECT e2.empcode FROM sd_employees e2 RIGHT JOIN sd_regionmanager rm2 "
						+ "ON e2.orgname LIKE '%' || rm2.regionname || '%' WHERE UPPER (rm2.workno) = UPPER ('"
						+ empCode + "') " + "AND e2.EMPCODE <> upper('" + empCode
						+ "') ) ee2 ON UPPER (o2.creatorno) = UPPER (ee2.empcode) "
						+ "UNION SELECT o3.* FROM sd_orders o3 WHERE UPPER (o3.creatorno) LIKE UPPER ('%" + empCode
						+ "%') " + "OR UPPER (o3.handleno) LIKE UPPER ('%" + empCode + "%') "
						+ "UNION SELECT o4.* FROM sd_orders o4 WHERE o4.id=ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "')) ) oo WHERE 1=1 and oo.status <> -99 ";

				countSql = "SELECT count(1) FROM ( SELECT o1.* FROM sd_orders o1 RIGHT JOIN ("
						+ "SELECT e1.empcode FROM sd_employees e1 RIGHT JOIN sd_regionmanager rm1 "
						+ "ON e1.orgname LIKE '%' || rm1.regionname || '%' WHERE UPPER (rm1.workno) = UPPER ('"
						+ empCode + "') " + "AND e1.EMPCODE <> upper('" + empCode
						+ "') ) ee1 ON UPPER (o1.handleno) = UPPER (ee1.empcode) "
						+ "UNION SELECT o2.* FROM sd_orders o2 RIGHT JOIN ("
						+ "SELECT e2.empcode FROM sd_employees e2 RIGHT JOIN sd_regionmanager rm2 "
						+ "ON e2.orgname LIKE '%' || rm2.regionname || '%' WHERE UPPER (rm2.workno) = UPPER ('"
						+ empCode + "') " + "AND e2.EMPCODE <> upper('" + empCode
						+ "') ) ee2 ON UPPER (o2.creatorno) = UPPER (ee2.empcode) "
						+ "UNION SELECT o3.* FROM sd_orders o3 WHERE UPPER (o3.creatorno) LIKE UPPER ('%" + empCode
						+ "%') " + "OR UPPER (o3.handleno) LIKE UPPER ('%" + empCode + "%')"
						+ "UNION SELECT o4.* FROM sd_orders o4 WHERE o4.id=ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "')) ) oo WHERE 1=1 and oo.status <> -99 ";

			} else {
				// 工程师或员工，自己管理的工单，对应状态
				sql = "select oo.* from sd_orders oo where ((upper(oo.handleno)=upper('" + empCode
						+ "') or upper(oo.creatorno) = upper('" + empCode
						+ "') ) or oo.id = ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "'))) and oo.status <> -99 ";

				countSql = "select count(1) from sd_orders oo where ((upper(oo.handleno)=upper('" + empCode
						+ "') or upper(oo.creatorno) = upper('" + empCode
						+ "') ) or oo.id = ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "'))) and oo.status <> -99 ";
			}

			if (status >= 0) {
				sql += " and oo.status=" + status;
				countSql += " and oo.status=" + status;
			}

			if (StringUtils.isNotBlank(creator)) {
				sql += " and  (upper(oo.creatorno) LIKE UPPER ('%" + creator + "%')  or oo.creator like '%" + creator
						+ "%')";
				countSql += " and (upper(oo.creatorno) LIKE UPPER ('%" + creator + "%') or oo.creator like '%" + creator
						+ "%') ";
			}

			if (StringUtils.isNotBlank(handler)) {
				sql += " and (upper(oo.handleno) LIKE UPPER ('%" + handler + "%')  or oo.handle like '%" + handler
						+ "%')";
				countSql += " and (upper(oo.handleno) LIKE UPPER ('%" + handler + "%') or oo.handle like '%" + handler
						+ "%')  ";
			}

			if (StringUtils.isNotBlank(orderNo)) {
				sql += " and upper(oo.orderNo) LIKE UPPER ('%" + orderNo + "%')";
				countSql += " and upper(oo.orderNo) LIKE UPPER ('%" + orderNo + "%') ";
			}

			if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
				sql += "and createTime between to_date('" + startDate + " 00:00:00"
						+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
						+ "','yyyy-mm-dd hh24:mi:ss')";

				countSql += "and createTime between to_date('" + startDate + " 00:00:00"
						+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
						+ "','yyyy-mm-dd hh24:mi:ss')";
			}

			if (StringUtils.isNotBlank(sql)) {
				sql += " order by oo.modifyTime desc";
			}

			Integer total = serviceOrdersDao.findTotalRowBySql(countSql);
			List<ServiceOrders> list = serviceOrdersDao.findPagerBySql(sql, pageNo, pageSize);
			Pager<ServiceOrders> pager = new Pager<ServiceOrders>();
			pager.setPagerList(list);
			pager.setTotalRow(total);

			return pager;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<ServiceOrders> findOrdersByUserRole(Integer userRole, String workNo, String query, int status,
			int pageSize, int pageNo) throws Exception {
		String sql = "";
		String countSql = "";
		if (userRole == 2) {
			// 服务经理，获取所在办事情下属员工的工单，对应状态

			sql = "SELECT oo.* FROM ( SELECT o1.* FROM sd_orders o1 RIGHT JOIN ("
					+ "SELECT e1.empcode FROM sd_employees e1 RIGHT JOIN sd_regionmanager rm1 "
					+ "ON e1.orgname LIKE '%' || rm1.regionname || '%' WHERE UPPER (rm1.workno) = UPPER ('" + workNo
					+ "') " + "AND e1.EMPCODE <> upper('" + workNo
					+ "') ) ee1 ON UPPER (o1.handleno) = UPPER (ee1.empcode) "
					+ "UNION SELECT o2.* FROM sd_orders o2 RIGHT JOIN ("
					+ "SELECT e2.empcode FROM sd_employees e2 RIGHT JOIN sd_regionmanager rm2 "
					+ "ON e2.orgname LIKE '%' || rm2.regionname || '%' WHERE UPPER (rm2.workno) = UPPER ('" + workNo
					+ "') " + "AND e2.EMPCODE <> upper('" + workNo
					+ "') ) ee2 ON UPPER (o2.creatorno) = UPPER (ee2.empcode) "
					+ "UNION SELECT o3.* FROM sd_orders o3 WHERE UPPER (o3.creatorno) LIKE UPPER ('%" + workNo + "%') "
					+ "OR UPPER (o3.handleno) LIKE UPPER ('%" + workNo + "%')"
					+ "UNION SELECT o4.* FROM sd_orders o4 WHERE o4.id=ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
					+ workNo + "'))) oo WHERE ";

			countSql = "SELECT count(1) FROM ( SELECT o1.* FROM sd_orders o1 RIGHT JOIN ("
					+ "SELECT e1.empcode FROM sd_employees e1 RIGHT JOIN sd_regionmanager rm1 "
					+ "ON e1.orgname LIKE '%' || rm1.regionname || '%' WHERE UPPER (rm1.workno) = UPPER ('" + workNo
					+ "') " + "AND e1.EMPCODE <> upper('" + workNo
					+ "') ) ee1 ON UPPER (o1.handleno) = UPPER (ee1.empcode) "
					+ "UNION SELECT o2.* FROM sd_orders o2 RIGHT JOIN ("
					+ "SELECT e2.empcode FROM sd_employees e2 RIGHT JOIN sd_regionmanager rm2 "
					+ "ON e2.orgname LIKE '%' || rm2.regionname || '%' WHERE UPPER (rm2.workno) = UPPER ('" + workNo
					+ "') " + "AND e2.EMPCODE <> upper('" + workNo
					+ "') ) ee2 ON UPPER (o2.creatorno) = UPPER (ee2.empcode) "
					+ "UNION SELECT o3.* FROM sd_orders o3 WHERE UPPER (o3.creatorno) LIKE UPPER ('%" + workNo + "%') "
					+ "OR UPPER (o3.handleno) LIKE UPPER ('%" + workNo + "%')"
					+ "UNION SELECT o4.* FROM sd_orders o4 WHERE o4.id=ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
					+ workNo + "')) ) oo WHERE ";

		} else {
			// 工程师或员工，自己管理的工单，对应状态
			sql = "select oo.* from sd_orders oo where ((upper(oo.handleno)=upper('" + workNo
					+ "') or upper(oo.creatorno) = upper('" + workNo
					+ "')) or oo.id = ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
					+ workNo + "'))) and ";

			countSql = "select count(1) from sd_orders oo where ((upper(oo.handleno)=upper('" + workNo
					+ "') or upper(oo.creatorno) = upper('" + workNo
					+ "')) or oo.id = ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
					+ workNo + "'))) and ";
		}

		if (5 == status) {
			sql += " (oo.status=" + status + " or oo.status = 4)";
			countSql += " (oo.status=" + status + " or oo.status = 4)";
		} else {
			if (0 == status) {
				sql += " (oo.status=" + status + " or oo.status = 3)";
				countSql += " (oo.status=" + status + " or oo.status = 3)";
			} else {
				sql += " oo.status=" + status;
				countSql += " oo.status=" + status;
			}
		}

		if (StringUtils.isNotBlank(query)) {
			sql += " and  (oo.orderFrom like '%" + query + "%' or oo.taskDesc like '%" + query
					+ "%' or oo.handleno like upper('%" + query + "%') or oo.handle like upper('%" + query + "%') )";
			countSql += " and (oo.orderFrom like '%" + query + "%' or oo.taskDesc like '%" + query
					+ "%' or oo.handleno like upper('%" + query + "%') or oo.handle like upper('%" + query + "%') )";
		}

		if (StringUtils.isNotBlank(sql)) {
			sql += " order by oo.modifyTime desc";
		}
		Integer total = serviceOrdersDao.findTotalRowBySql(countSql);
		List<ServiceOrders> list = serviceOrdersDao.findPagerBySql(sql, pageNo, pageSize);
		Pager<ServiceOrders> pager = new Pager<ServiceOrders>();
		pager.setPagerList(list);
		pager.setTotalRow(total);

		return pager;
	}

	@Override
	public Map<String, String> statisticsOrderCount(String empCode) {
		try {
			Map<String, String> countMap = new HashMap<String, String>();
			// 查询个人新建工单记录数
			String addCountSql = "select count(*) from sd_orders where status <> -99 and creatorNo = upper('" + empCode + "')";
			Integer addCount = serviceOrdersDao.findTotalRowBySql(addCountSql);
			countMap.put("addCount", addCount + "");
			// 查询中
			String applyCountSql = "select count(*) from sd_orders where status <> -99 and handleNo = upper('" + empCode + "')";
			Integer applyCount = serviceOrdersDao.findTotalRowBySql(applyCountSql);
			countMap.put("applyCount", applyCount + "");
			// 已完成
			String completeCountSql = "select count(*) from sd_orders where status <> -99 and (handleNo = upper('" + empCode
					+ "') or creatorNo= upper('" + empCode + "')) and (status = 4 or status = 5)";
			Integer completeCount = serviceOrdersDao.findTotalRowBySql(completeCountSql);
			countMap.put("completeCountSql", completeCount + "");

			return countMap;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ServiceOrders findLastOrderNo() {
		try {
			String sql = "select * from (select * from sd_orders  order by createtime desc ) where ROWNUM <= 1";
			List<ServiceOrders> list = serviceOrdersDao.findBySql(sql);
			if (list != null && !list.isEmpty()) {
				return list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Integer getTotalByStatus(int status, String date) {
		try {
			String sql = "select count(1) from sd_orders where endTime > to_date('" + date
					+ "','yyyy-mm-dd hh24:mi:ss') and status = " + status;
			return serviceOrdersDao.findTotalRowBySql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean updateOrderStatus(int status, String date) {
		try {
			String hql = "update ServiceOrders set status = " + status + " where endTime > to_date('" + date
					+ "','yyyy-mm-dd hh24:mi:ss') and parentId = 0 ";
			return serviceOrdersDao.updateByHql(hql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public boolean updateOrderStatusByOrderId(int orderId, ServiceOrdersLogs sol) {
		try {
			String hql = "update ServiceOrders set status = -99 ,modifyTime = sysdate where id =" + orderId;
			boolean result = serviceOrdersDao.updateByHql(hql);
			if (result) {
				sol.setOrderId(orderId);
				serviceOrdersLogsDao.save(sol);
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<ServiceOrders> findOrdersByStauts(int status, String date) {
		try {
			String sql = "select * from sd_orders where status = " + status
					+ " and parentId = 0 and  endTime <= to_date('" + date + "','yyyy-mm-dd hh24:mi:ss')";
			logger.debug("========sql:" + sql);
			return serviceOrdersDao.findBySql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<ServiceOrders> findChildOrdersByStauts(int status, String date) {
		try {
			String sql = "select * from sd_orders where status = " + status
					+ " and parentId <> 0 and  endTime <= to_date('" + date + "','yyyy-mm-dd hh24:mi:ss')";
			logger.debug("========sql:" + sql);
			return serviceOrdersDao.findBySql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<ServiceOrders> findOrdersByParentId(int parentId) {
		try {
			String sql = "select * from sd_orders where status not in ('5','-99') and parentId = " + parentId
					+ " order by modifyTime desc";
			return serviceOrdersDao.findBySql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<ServiceOrders> exportAllOrdersByWeb(Integer userRole,
			String empCode, String creator, String handler, String startDate,
			String endDate, int status, String searchOrderNo) {
		String sql = "";
		try {
			if (userRole == 2) {
				// 服务经理，获取所在办事情下属员工的工单，对应状态
				sql = "SELECT oo.* FROM ( SELECT o1.* FROM sd_orders o1 RIGHT JOIN ("
						+ "SELECT e1.empcode FROM sd_employees e1 RIGHT JOIN sd_regionmanager rm1 "
						+ "ON e1.orgname LIKE '%' || rm1.regionname || '%' WHERE UPPER (rm1.workno) = UPPER ('"
						+ empCode + "') " + "AND e1.EMPCODE <> upper('" + empCode
						+ "') ) ee1 ON UPPER (o1.handleno) = UPPER (ee1.empcode) "
						+ "UNION SELECT o2.* FROM sd_orders o2 RIGHT JOIN ("
						+ "SELECT e2.empcode FROM sd_employees e2 RIGHT JOIN sd_regionmanager rm2 "
						+ "ON e2.orgname LIKE '%' || rm2.regionname || '%' WHERE UPPER (rm2.workno) = UPPER ('"
						+ empCode + "') " + "AND e2.EMPCODE <> upper('" + empCode
						+ "') ) ee2 ON UPPER (o2.creatorno) = UPPER (ee2.empcode) "
						+ "UNION SELECT o3.* FROM sd_orders o3 WHERE UPPER (o3.creatorno) LIKE UPPER ('%" + empCode
						+ "%') " + "OR UPPER (o3.handleno) LIKE UPPER ('%" + empCode + "%') "
						+ "UNION SELECT o4.* FROM sd_orders o4 WHERE o4.id=ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "')) ) oo WHERE 1=1 and oo.status <> -99 ";
			} else {
				// 工程师或员工，自己管理的工单，对应状态
				sql = "select oo.* from sd_orders oo where ((upper(oo.handleno)=upper('" + empCode
						+ "') or upper(oo.creatorno) = upper('" + empCode
						+ "') ) or oo.id = ANY(SELECT orderId FROM sd_order_cc_relation WHERE UPPER(ccUsername)= UPPER('"
						+ empCode + "'))) and oo.status <> -99 ";
			}

			if (status >= 0) {
				sql += " and oo.status=" + status;
			}

			if (StringUtils.isNotBlank(creator)) {
				sql += " and  (upper(oo.creatorno) LIKE UPPER ('%" + creator + "%')  or oo.creator like '%" + creator
						+ "%')";
			}

			if (StringUtils.isNotBlank(handler)) {
				sql += " and (upper(oo.handleno) LIKE UPPER ('%" + handler + "%')  or oo.handle like '%" + handler
						+ "%')";
			}

			if (StringUtils.isNotBlank(searchOrderNo)) {
				sql += " and upper(oo.orderNo) LIKE UPPER ('%" + searchOrderNo + "%')";
			}

			if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
				sql += "and createTime between to_date('" + startDate + " 00:00:00"
						+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endDate + " 23:59:59"
						+ "','yyyy-mm-dd hh24:mi:ss')";
			}

			if (StringUtils.isNotBlank(sql)) {
				sql += " order by oo.modifyTime desc";
			}
			
			return serviceOrdersDao.findBySql(sql.toString());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	@Override
	public ServiceOrders findOrderByOrderNo(String orderNo) {
		try {
			String sql = "select * from sd_orders so where so.orderNo = '" + orderNo +"' ";
			List<ServiceOrders> list = serviceOrdersDao.findBySql(sql);
			if (list != null && !list.isEmpty()) {
				return list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
